﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace DAL
{
    [Serializable]
    public class UserExData
    {

        public const string USERIDField = "@UserID";

        public const string PRIMARYKEY_Field = @"UserID";

        public const string USERNAMEField = "@UserName";

        public const string CONTACTField = "@Contact";

        public const string TELEPHONEField = "@Telephone";

        public const string USERLEVELIDField = "@UserLevelID";

        public const string MEASUREMENTCOUNTLIMITField = "@MeasurementCountLimit";

        public const string CURRENTMEASUREMENTCOUNTField = "@CurrentMeasurementCount";

        public const string REMARKField = "@Remark";

        public const string CREATORField = "@Creator";

        public const string CREATETIMEField = "@CreateTime";

        public const string UPDATETIMEField = "@UpdateTime";

        public const string AUTHORITYField = "@Authority";

        public const string ISAVAILABLEField = "@IsAvailable";

        private Guid _UserID;

        private String _UserName;

        private String _Contact;

        private String _Telephone;

        private Guid _UserLevelID;

        private Int32 _MeasurementCountLimit;

        private Int32 _CurrentMeasurementCount;

        private String _Remark;

        private String _Creator;

        private DateTime _CreateTime;

        private DateTime _UpdateTime;

        private String _Authority;

        private Boolean _IsAvailable;

        public Guid UserID
        {
            get { return _UserID; }
            set { _UserID = value; }
        }
        public String UserName
        {
            get { return _UserName; }
            set { _UserName = value; }
        }
        public String Contact
        {
            get { return _Contact; }
            set { _Contact = value; }
        }
        public String Telephone
        {
            get { return _Telephone; }
            set { _Telephone = value; }
        }
        public Guid UserLevelID
        {
            get { return _UserLevelID; }
            set { _UserLevelID = value; }
        }
        public Int32 MeasurementCountLimit
        {
            get { return _MeasurementCountLimit; }
            set { _MeasurementCountLimit = value; }
        }
        public Int32 CurrentMeasurementCount
        {
            get { return _CurrentMeasurementCount; }
            set { _CurrentMeasurementCount = value; }
        }
        public String Remark
        {
            get { return _Remark; }
            set { _Remark = value; }
        }
        public String Creator
        {
            get { return _Creator; }
            set { _Creator = value; }
        }
        public DateTime CreateTime
        {
            get { return _CreateTime; }
            set { _CreateTime = value; }
        }
        public DateTime UpdateTime
        {
            get { return _UpdateTime; }
            set { _UpdateTime = value; }
        }
        public String Authority
        {
            get { return _Authority; }
            set { _Authority = value; }
        }
        public Boolean IsAvailable
        {
            get { return _IsAvailable; }
            set { _IsAvailable = value; }
        }
        public UserExData()
        {
            _UserID = Guid.Empty;
            _UserName = "";
            _Contact = "";
            _Telephone = "";
            _UserLevelID = Guid.Empty;
            _MeasurementCountLimit = 1000;
            _CurrentMeasurementCount = 0;
            _Remark = "";
            _Creator = "";
            _CreateTime = DateTime.Now;
            _UpdateTime = DateTime.Now;
            _Authority = "";
            _IsAvailable = true;
        }
    }

    public class UserExDataFactory
    {
        public static UserExData Construct(IDataReader reader)
        {
            UserExData data = new UserExData();

            try { data.UserID = (Guid)(reader["UserID"]); }
            catch { }

            try { data.UserName = Convert.ToString(reader["UserName"]); }
            catch { }

            try { data.Contact = Convert.ToString(reader["Contact"]); }
            catch { }

            try { data.Telephone = Convert.ToString(reader["Telephone"]); }
            catch { }

            try { data.UserLevelID = (Guid)(reader["UserLevelID"]); }
            catch { }

            try { data.MeasurementCountLimit = Convert.ToInt32(reader["MeasurementCountLimit"]); }
            catch { }

            try { data.CurrentMeasurementCount = Convert.ToInt32(reader["CurrentMeasurementCount"]); }
            catch { }

            try { data.Remark = Convert.ToString(reader["Remark"]); }
            catch { }

            try { data.Creator = Convert.ToString(reader["Creator"]); }
            catch { }

            try { data.CreateTime = Convert.ToDateTime(reader["CreateTime"]); }
            catch { }

            try { data.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]); }
            catch { }

            try { data.Authority = Convert.ToString(reader["Authority"]); }
            catch { }

            try { data.IsAvailable = Convert.ToBoolean(reader["IsAvailable"]); }
            catch { }

            return data;
        }
        public static Guid GetPrimaryKey(DataTable dt)
        {
            return GetPrimaryKey(dt, 0);
        }
        public static Guid GetPrimaryKey(DataTable dt, int rowIndex)
        {
            Guid PrimaryKey = Guid.Empty;

            if (dt.Rows.Count < rowIndex + 1)

                return Guid.Empty;

            DataRow dr = dt.Rows[rowIndex];

            if (dr == null)

                return Guid.Empty;

            try { PrimaryKey = (Guid)(dr[UserExData.PRIMARYKEY_Field]); }
            catch { }

            return PrimaryKey;
        }
        public static UserExData Construct(DataTable dt)
        {
            return Construct(dt, 0);
        }
        public static UserExData Construct(DataTable dt, int rowIndex)
        {
            UserExData data = new UserExData();

            if (dt.Rows.Count < rowIndex + 1)

                return null;

            DataRow dr = dt.Rows[rowIndex];

            if (dr == null)

                return null;

            try { data.UserID = new Guid(dr["UserID"].ToString()); }
            catch { }

            try { data.UserName = Convert.ToString(dr["UserName"]); }
            catch { }

            try { data.Contact = Convert.ToString(dr["Contact"]); }
            catch { }

            try { data.Telephone = Convert.ToString(dr["Telephone"]); }
            catch { }

            try { data.UserLevelID = new Guid(dr["UserLevelID"].ToString()); }
            catch { }

            try { data.MeasurementCountLimit = Convert.ToInt32(dr["MeasurementCountLimit"]); }
            catch { }

            try { data.CurrentMeasurementCount = Convert.ToInt32(dr["CurrentMeasurementCount"]); }
            catch { }

            try { data.Remark = Convert.ToString(dr["Remark"]); }
            catch { }

            try { data.Creator = Convert.ToString(dr["Creator"]); }
            catch { }

            try { data.CreateTime = Convert.ToDateTime(dr["CreateTime"]); }
            catch { }

            try { data.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]); }
            catch { }

            try { data.Authority = Convert.ToString(dr["Authority"]); }
            catch { }

            try { data.IsAvailable = Convert.ToBoolean(dr["IsAvailable"]); }
            catch { }

            return data;
        }
        public static UserExData Construct(DataRow dr)
        {
            UserExData data = new UserExData();

            try { data.UserID = new Guid(dr["UserID"].ToString()); }
            catch { }

            try { data.UserName = Convert.ToString(dr["UserName"]); }
            catch { }

            try { data.Contact = Convert.ToString(dr["Contact"]); }
            catch { }

            try { data.Telephone = Convert.ToString(dr["Telephone"]); }
            catch { }

            try { data.UserLevelID = new Guid(dr["UserLevelID"].ToString()); }
            catch { }

            try { data.MeasurementCountLimit = Convert.ToInt32(dr["MeasurementCountLimit"]); }
            catch { }

            try { data.CurrentMeasurementCount = Convert.ToInt32(dr["CurrentMeasurementCount"]); }
            catch { }

            try { data.Remark = Convert.ToString(dr["Remark"]); }
            catch { }

            try { data.Creator = Convert.ToString(dr["Creator"]); }
            catch { }

            try { data.CreateTime = Convert.ToDateTime(dr["CreateTime"]); }
            catch { }

            try { data.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]); }
            catch { }

            try { data.Authority = Convert.ToString(dr["Authority"]); }
            catch { }

            try { data.IsAvailable = Convert.ToBoolean(dr["IsAvailable"]); }
            catch { }

            return data;
        }
        public static Guid GetPrimaryKey(DataRow dr)
        {
            Guid PrimaryKey = Guid.Empty;

            try { PrimaryKey = (Guid)(dr[UserExData.PRIMARYKEY_Field]); }
            catch { }
            return PrimaryKey;
        }

    }
    public class UserExDataDBOption
    {
        public static int Insert(UserExData d)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into UserEx (");
            sql.Append("UserID, ");
            sql.Append("UserName, ");
            sql.Append("Contact, ");
            sql.Append("Telephone, ");
            sql.Append("UserLevelID, ");
            sql.Append("MeasurementCountLimit, ");
            sql.Append("CurrentMeasurementCount, ");
            sql.Append("Remark, ");
            sql.Append("Creator, ");
            sql.Append("CreateTime, ");
            sql.Append("UpdateTime, ");
            sql.Append("Authority, ");
            sql.Append("IsAvailable");
            sql.Append(") values (");
            sql.Append("'");
            sql.Append(d.UserID.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.UserName.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.Contact.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.Telephone.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.UserLevelID.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("");
            sql.Append(d.MeasurementCountLimit.ToString());
            sql.Append("");
            sql.Append(", ");
            sql.Append("");
            sql.Append(d.CurrentMeasurementCount.ToString());
            sql.Append("");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.Remark.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.Creator.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            sql.Append("'");
            sql.Append(", ");
            sql.Append("'");
            sql.Append(d.Authority.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("");
            if (d.IsAvailable)
                sql.Append("1");
            else
                sql.Append("0");
            sql.Append("");
            sql.Append(")");
            return WFNetLib.ADO.SQLServerOP.NonQuerySQL(sql.ToString());
        }
        public static int Update(UserExData d)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update UserEx set ");
            sql.Append("UserName=");
            sql.Append("'");
            sql.Append(d.UserName.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("Contact=");
            sql.Append("'");
            sql.Append(d.Contact.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("Telephone=");
            sql.Append("'");
            sql.Append(d.Telephone.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("UserLevelID=");
            sql.Append("'");
            sql.Append(d.UserLevelID.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("MeasurementCountLimit=");
            sql.Append("");
            sql.Append(d.MeasurementCountLimit.ToString());
            sql.Append("");
            sql.Append(", ");
            sql.Append("CurrentMeasurementCount=");
            sql.Append("");
            sql.Append(d.CurrentMeasurementCount.ToString());
            sql.Append("");
            sql.Append(", ");
            sql.Append("Remark=");
            sql.Append("'");
            sql.Append(d.Remark.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("Creator=");
            sql.Append("'");
            sql.Append(d.Creator.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("CreateTime=");
            sql.Append("'");
            sql.Append(d.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            sql.Append("'");
            sql.Append(", ");
            sql.Append("UpdateTime=");
            sql.Append("'");
            sql.Append(d.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            sql.Append("'");
            sql.Append(", ");
            sql.Append("Authority=");
            sql.Append("'");
            sql.Append(d.Authority.ToString());
            sql.Append("'");
            sql.Append(", ");
            sql.Append("IsAvailable=");
            sql.Append("");
            if (d.IsAvailable)
                sql.Append("1");
            else
                sql.Append("0");
            sql.Append("");
            sql.Append(" where UserID='");
            sql.Append(d.UserID.ToString());
            sql.Append("'");
            return WFNetLib.ADO.SQLServerOP.NonQuerySQL(sql.ToString());
        }
        public static int Delete(UserExData d)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from UserEx ");
            sql.Append(" where UserID='");
            sql.Append(d.UserID.ToString());
            sql.Append("'");
            return WFNetLib.ADO.SQLServerOP.NonQuerySQL(sql.ToString());
        }
        public static DataTable DataTableSelect()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx ");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataTable DataTableSelect(UserExData d)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx where UserID=");
            sql.Append(d.UserID.ToString());
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static DataSet DataSetSelect()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx ");
            return WFNetLib.ADO.SQLServerOP.DataSetSQL(sql.ToString());
        }
        public static SqlDataReader ReaderSelect(UserExData d)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx ");
            return WFNetLib.ADO.SQLServerOP.ReaderExecuteSQL(sql.ToString());
        }


        public static UserExData SelectByUserID(Guid userid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx where UserID='");
            sql.Append(userid.ToString());
            sql.Append("'");
            DataTable dt = WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
            if (dt.Rows.Count != 0)
                return UserExDataFactory.Construct(dt.Rows[0]);
            else
                return null;
        }

        public static DataTable SelectByFrameID(Guid frameid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from UserEx where UserLevelID='");
            sql.Append(frameid.ToString());
            sql.Append("'");
            return WFNetLib.ADO.SQLServerOP.DataTableSQL(sql.ToString());
        }
        public static int DeleteByUserID(Guid id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from UserEx ");
            sql.Append(" where UserID='");
            sql.Append(id.ToString());
            sql.Append("'");
            return WFNetLib.ADO.SQLServerOP.NonQuerySQL(sql.ToString());
        }
    }
}